import sqlite3

import pandas as pd
import requests
from bs4 import BeautifulSoup
import json

lst1 = []
lst2 = []
lst3 = []
lst4 = []


def getMovies(url):
    # 伪装的头请求
    # head   cookie
    global var, df
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36 Edg/117.0.2045.41'}
    # 将URL中的参数封装到字典中
    data = {
        'start': '0',
        'filter': '',
    }

    response = requests.get(url, headers=headers, data=data)
    response.encoding = 'utf-8'
    response.raise_for_status()
    response.cookies

    bs = BeautifulSoup(response.text, 'html.parser')

    vars = bs.find_all('div', class_='info')
    # print(vars)
    for var in vars:
        filmName = var.find('span').text
        filmScore = var.find('span', class_='rating_num').text
        inq = var.find('span', class_='inq').text
        playable_spans = var.find_all('span', class_='playable')
        playable = '' if not playable_spans else playable_spans[0].text.strip().replace('[', '').replace(']', '')

        lst1.append(filmName)
        lst2.append(filmScore)
        lst3.append(inq)
        lst4.append(playable)

    data = {'filmName': lst1,
            'filmScore': lst2,
            'inq': lst3,
            'playable': lst4}
    df = pd.DataFrame(data)

    df['playable'] = df['playable'].apply(lambda x: x if x else '暂无资源')

    print(df)

    try:
        with sqlite3.connect('movies.db') as conn:
            c = conn.cursor()

            # 检查表是否存在并删除
            c.execute('DROP TABLE IF EXISTS films')

            # 创建新表
            c.execute('''CREATE TABLE films     
                             (filmId INTEGER PRIMARY KEY AUTOINCREMENT,    
                              filmName TEXT,    
                              filmScore TEXT,    
                              inq TEXT,    
                              playable TEXT)''')

            # 插入数据
            for index, row in df.iterrows():
                c.execute("INSERT INTO films (filmName, filmScore, inq, playable) VALUES (?, ?, ?, ?)",
                          (row['filmName'], row['filmScore'], row['inq'], row['playable']))

                # 提交事务（在 with 语句块结束时自动提交）
            # conn.commit()  # 这一行在 with 语句中是不必要的

        print("数据已成功保存到数据库。")
    except sqlite3.Error as e:
        print(f"数据库操作出错: {e}")
    except Exception as e:
        print(f"发生错误: {e}")
# 查询评论
if __name__ == '__main__':
    getMovies("https://movie.douban.com/top250")
    getMovies("https://movie.douban.com/top250?start=25&filter=")
    getMovies("https://movie.douban.com/top250?start=50&filter=")
    getMovies("https://movie.douban.com/top250?start=75&filter=")
    # getMovies("https://movie.douban.com/top250?start=100&filter=")
